<!DOCTYPE HTML PUBLIC "-//W3C//DTD HTML 4.01 Transitional//EN"> 
<html>
<head>
<title>SQL</title>
<link rel="stylesheet" href="/cfg/format.css" type="text/css">
<meta http-equiv="content-type" content="text/html; charset=utf-8">
<meta name="keywords" content="Apache Derby, Java, tutorial, database, SQL">
<meta name="description" content="In this chapter of the Apache Derby tutorial,
we cover the SQL language understood by the Derby database.">
<meta name="language" content="en">
<meta name="author" content="Jan Bodnar">
<meta name="distribution" content="global">

<script type="text/javascript" src="/lib/jquery.js"></script>
<script type="text/javascript" src="/lib/common.js"></script>

</head>

<body>

<div class="container">

<div id="wide_ad" class="ltow">
<script type="text/javascript"><!--
google_ad_client = "pub-9706709751191532";
/* 160x600, August 2011 */
google_ad_slot = "2484182563";
google_ad_width = 160;
google_ad_height = 600;
//-->
</script>
<script type="text/javascript"
src="http://pagead2.googlesyndication.com/pagead/show_ads.js">
</script>
</div>


<div class="content">


<a href="/" title="Home">Home</a>&nbsp;
<a href="..">Contents</a>


<h1>SQL</h1>

<p>
In the following pages, we will work with the SQL understood by the Derby 
database engine. We will not go into much detail about the SQL language. 
This chapter is a quick recap of the most important SQL statements present
in the Derby database.
</p>

<script type="text/javascript"><!--
google_ad_client = "pub-9706709751191532";
/* NewSquare */
google_ad_slot = "0364418177";
google_ad_width = 300;
google_ad_height = 250;
//-->
</script> 
<script type="text/javascript"
src="http://pagead2.googlesyndication.com/pagead/show_ads.js"> 
</script> 


<p>
<b>SQL (Structured Query Language)</b> is a database computer language designed 
for managing data in relational database management systems. Derby supports only
a limited set of SQL statements. Some important statements known from other
database systems are missing. Derby implements an SQL-92 core subset, 
as well as some SQL-99 features.
</p>

<pre class="code">
ij> DROP TABLE AUTHORS;
0 rows inserted/updated/deleted
ij> DROP TABLE BOOKS;
0 rows inserted/updated/deleted
</pre>

<p>
We have previously created the AUTHORS and BOOKS tables. We are going to 
recreate them. The DROP TABLE SQL statement drops the table from the database. Note
that the DROP TABLE IF EXISTS statement does not exist in Derby.
</p>

<pre class="code">
ij> CREATE TABLE AUTHORS(ID INT PRIMARY KEY, NAME VARCHAR(25));
0 rows inserted/updated/deleted
</pre>

<p>
The CREATE TABLE statement creates a new table. It has two columns, ID and
NAME. In the ID column we will place integers, in the NAME column
strings with up to 25 characters. A PRIMARY KEY uniquely identifies each 
record in the table. Each author is a unique personality. Even if there are authors
with the same name, each of them is in a separate row in the AUTHORS table.
Only one column in a table can have this constraint. 
</p>

<pre class="code">
ij> CREATE TABLE BOOKS(ID INT PRIMARY KEY, AUTHOR_ID INT, 
> TITLE VARCHAR(100), FOREIGN KEY(AUTHOR_ID) REFERENCES AUTHORS(ID));
0 rows inserted/updated/deleted
</pre>

<p>
We create a BOOKS table, which has three columns. The FOREIGN KEY specifies
that the values in the AUTHOR_ID column must match the values in the ID
column of the AUTHORS table. Foreign keys provide a way to enforce the 
referential integrity of a database. Each book was written by one or
more authors. So in the BOOKS table for the AUTHOR_ID column we can have 
only values that are present in the AUTHORS table. 
</p>

<pre class="code">
ij> INSERT INTO AUTHORS(ID, NAME) VALUES(1, 'Jack London');
ij> INSERT INTO AUTHORS(ID, NAME) VALUES(2, 'Honore de Balzac');
ij> INSERT INTO AUTHORS(ID, NAME) VALUES(3, 'Lion Feuchtwanger');
ij> INSERT INTO AUTHORS(ID, NAME) VALUES(4, 'Emile Zola');
ij> INSERT INTO AUTHORS(ID, NAME) VALUES(5, 'Truman Capote');
</pre>

<p>
We add five rows to the AUTHORS table using the INSERT INTO SQL statement.
</p>

<pre class="code">
ij> INSERT INTO BOOKS(ID, AUTHOR_ID, TITLE) VALUES(1, 1, 'Call of the Wild');
ij> INSERT INTO BOOKS(ID, AUTHOR_ID, TITLE) VALUES(2, 1, 'Martin Eden');
ij> INSERT INTO BOOKS(ID, AUTHOR_ID, TITLE) VALUES(3, 2, 'Old Goriot');
ij> INSERT INTO BOOKS(ID, AUTHOR_ID, TITLE) VALUES(4, 2, 'Cousin Bette');
ij> INSERT INTO BOOKS(ID, AUTHOR_ID, TITLE) VALUES(5, 3, 'Jew Suess');
ij> INSERT INTO BOOKS(ID, AUTHOR_ID, TITLE) VALUES(6, 4, 'Nana');
ij> INSERT INTO BOOKS(ID, AUTHOR_ID, TITLE) VALUES(7, 4, 'The Belly of Paris');
ij> INSERT INTO BOOKS(ID, AUTHOR_ID, TITLE) VALUES(8, 5, 'In Cold blood');
ij> INSERT INTO BOOKS(ID, AUTHOR_ID, TITLE) VALUES(9, 5, 'Breakfast at Tiffany');
</pre>

<p>
We insert 8 rows into the BOOKS table.
</p>

<pre class="code">
ij> SELECT NAME, TITLE FROM AUTHORS, BOOKS
> WHERE AUTHORS.ID = BOOKS.AUTHOR_ID;
NAME                     |TITLE                                                                                               
-------------------------------------------------
Jack London              |Call of the Wild                                                                                    
Jack London              |Martin Eden                                                                                         
Honore de Balzac         |Old Goriot                                                                                          
Honore de Balzac         |Cousin Bette                                                                                        
Lion Feuchtwanger        |Jew Suess                                                                                           
Emile Zola               |Nana                                                                                                
Emile Zola               |The Belly of Paris                                                                                  
Truman Capote            |In Cold blood                                                                                       
Truman Capote            |Breakfast at Tiffany                                                                                

9 rows selected
</pre>

<p>
The above SQL query joins the two tables. It assigns each book title
to an author.
</p>


<h2>Autoincrement</h2>

<p>
Derby allows to create an autoincrement column. The value of an autoincrement column 
increments automatically with every INSERT.
</p>

<pre class="code">
CREATE TABLE FRIENDS(ID INT PRIMARY KEY
    GENERATED ALWAYS AS IDENTITY
    (START WITH 1, INCREMENT BY 1), 
    NAME VARCHAR(20));

INSERT INTO FRIENDS(NAME) VALUES('Jane');
INSERT INTO FRIENDS(NAME) VALUES('Thomas');
INSERT INTO FRIENDS(NAME) VALUES('Beka');
</pre>

<p>
In the above SQL code, the ID column is an autoincrement column. 
If we omit the ID in the SQL INSERT statements the Derby automatically
generates a unique number. It starts with number 1 and is incremented
by one with each subsequent INSERT. 
</p>


<h2>Queries</h2>

<p>
Queries are used to look up data from the database tables. The SELECT
statement is the main statement to perform queries. 
</p>


<h3>Limiting data output</h3>

<p>
Limiting data output is essential since many databases have thousands
even millions of rows. Derby does not support the LIMIT clause known from
other databases. 
Derby 10.7 introduced FETCH and OFFSET clauses that do the same thing. 
</p>

<pre class="code">
ij> SELECT * FROM BOOKS FETCH FIRST 4 ROWS ONLY;
ID         |AUTHOR_ID  |TITLE                                                                                               
-------------------------------------------------
1          |1          |Call of the Wild                                                                                    
2          |1          |Martin Eden                                                                                         
3          |2          |Old Goriot                                                                                          
4          |2          |Cousin Bette 
</pre>

<p>
In the first example, we have fetched only the first 4 rows from the 
BOOKS table.
</p>

<pre class="code">
ij> SELECT * FROM BOOKS OFFSET 4 ROWS;
ID         |AUTHOR_ID  |TITLE                                                                                               
-----------------------------------------------
5          |3          |Jew Suess                                                                                           
6          |4          |Nana                                                                                                
7          |4          |The Belly of Paris                                                                                  
8          |5          |In Cold blood                                                                                       
9          |5          |Breakfast at Tiffany 
</pre>

<p>
With the OFFSET cluase, we skip the first four rows and display the rest.
</p>

<pre class="code">
ij> SELECT * FROM BOOKS OFFSET 4 ROWS FETCH NEXT 3 ROWS ONLY;
ID         |AUTHOR_ID  |TITLE                                                                                               
-----------------------------------------------------------------
5          |3          |Jew Suess                                                                                           
6          |4          |Nana                                                                                                
7          |4          |The Belly of Paris                                                                                  

3 rows selected
</pre>

<p>
We can select a portion of the rows using the combination of OFFSET and 
FETCH clauses. 
</p>


<h3>Derby functions</h3>

<p>
Derby supports a few useful functions. These built-in functions are 
expressions in which an SQL keyword or special operator executes some operation. 
</p>

<pre class="code">
ij> SELECT COUNT(ID) FROM AUTHORS;
1          
-----------
5   
</pre>

<p>
The COUNT() is an aggregate function that counts the number of rows accessed 
in an expression. There are 5 authors in the AUTHORS table.
</p>

<pre class="code">
ij> SELECT MIN(PRICE) AS "PRICE", MAX(PRICE) AS "MAX",
> AVG(PRICE) AS "AVG", SUM(PRICE) AS "SUM" FROM CARS;
PRICE      |MAX        |AVG        |SUM        
-----------------------------------------------
9000       |350000     |72721      |581769     

1 row selected
</pre>

<p>
In the above query we use other four functions. MAX(), MIN(), 
AVG() and SUM(). The AS clause gives a label for a column.
</p>

<pre class="code">
ij> VALUES CURRENT_DATE;
1         
----------
2012-02-15

ij> VALUES CURRENT SCHEMA;
1                                                                                                                               
--------------------------
USER12  
</pre>

<p>
VALUES CURRENT_DATE returns the current date and VALUES CURRENT SCHEMA 
returns the current schema of the connection.
</p>


<h2>Selecting specific rows with the WHERE clause</h2>

<p>
The WHERE clause can be used to filter the results. It provides a 
selection criteria to select only specific rows from the data.
</p>

<pre class="code">
ij> SELECT * FROM CARS WHERE PRICE > 40000;
ID         |NAME                          |PRICE      
------------------------------------------------------
1          |Audi                          |52642      
2          |Mercedes                      |57127      
5          |Bentley                       |350000     
7          |Hummer                        |41400

4 rows selected
</pre>

<p>
With the WHERE clause we only select the cars which have
a price higher than 40000. 
</p>

<pre class="code">
ij> SELECT NAME FROM CARS WHERE NAME LIKE '%en';
NAME                          
------------------------------
Citroen                       
Volkswagen                    

2 rows selected
</pre>

<p>
With a LIKE clause we select specific car names that fit the
search pattern. In our case it is cars that end in 'en' characters.
</p>

<pre class="code">
ij> SELECT * FROM CARS WHERE ID IN (2, 5, 7);
ID         |NAME                          |PRICE      
------------------------------------------------------
2          |Mercedes                      |57127      
5          |Bentley                       |350000     
7          |Hummer                        |41400      

3 rows selected
</pre>

<p>
The IN clause can be used to select rows from a specific range of values.
The above SQL statement returns rows that have IDs equal to 2, 5 and 7. 
</p>

<pre class="code">
ij> SELECT * FROM CARS WHERE PRICE BETWEEN 20000 AND 50000;
ID         |NAME                          |PRICE      
------------------------------------------------------
4          |Volvo                         |29000      
6          |Citroen                       |21000      
7          |Hummer                        |41400      
8          |Volkswagen                    |21600      

4 rows selected
</pre>

<p>
We select cars that cost in the range 20000 and 50000. For this
we use the BETWEEN AND keywords following the WHERE clause.
</p>


<h3>Ordering data</h3>

<p>
Ordering data can be done with the ORDER BY clause.
</p>

<pre class="code">
ij> SELECT * FROM CARS ORDER BY PRICE;
ID         |NAME                          |PRICE      
------------------------------------------------------
3          |Skoda                         |9000       
6          |Citroen                       |21000      
8          |Volkswagen                    |21600      
4          |Volvo                         |29000      
7          |Hummer                        |41400      
1          |Audi                          |52642      
2          |Mercedes                      |57127      
5          |Bentley                       |350000     

8 rows selected
</pre>

<p>
We order the cars by price. The default order type is ascending 
order.  
</p>

<pre class="code">
ij> SELECT * FROM CARS ORDER BY PRICE DESC;
ID         |NAME                          |PRICE      
------------------------------------------------------
5          |Bentley                       |350000     
2          |Mercedes                      |57127      
1          |Audi                          |52642      
7          |Hummer                        |41400      
4          |Volvo                         |29000      
8          |Volkswagen                    |21600      
6          |Citroen                       |21000      
3          |Skoda                         |9000  
</pre>

<p>
To order data in descending order, we add the DESC keyword. 
</p>


<h3>Updating and deleting data</h3>

<p>
Now we will concern ourselves with updating and deleting data in
the CARS table. 
</p>

<pre class="code">
ij> UPDATE CARS SET PRICE=58000 WHERE ID=2;
1 row inserted/updated/deleted
</pre>

<p>
The UPDATE statement is used to modify data in a database table.
The PRICE of a Mercedes car is set to 58000.
</p>

<pre class="code">
ij> SELECT * FROM CARS WHERE ID=2;
ID         |NAME                          |PRICE      
------------------------------------------------------
2          |Mercedes                      |58000      

1 row selected
</pre>

<p>
The subsequent SELECT statement confirms the modification of the data.
</p>

<hr class="btm">

<pre class="code">
ij> CREATE TABLE CARS2(ID INT PRIMARY KEY, NAME VARCHAR(30), PRICE INT);
</pre>

<p>
For the next case, we create a new CARS2 table. 
</p>

<pre class="code">
ij> INSERT INTO CARS2 SELECT * FROM CARS;
8 rows inserted/updated/deleted
</pre>

<p>
We insert all the rows from the CARS table into the CARS2 table, thus
copying all data. 
</p>

<pre class="code">
ij> SELECT * FROM CARS2;
ID         |NAME                          |PRICE      
------------------------------------------------------
1          |Audi                          |52642      
2          |Mercedes                      |58000      
3          |Skoda                         |9000       
4          |Volvo                         |29000      
5          |Bentley                       |350000     
6          |Citroen                       |21000      
7          |Hummer                        |41400      
8          |Volkswagen                    |21600      

8 rows selected
</pre>

<p>
We check the CARS2 table and see that all data was copying OK. 
</p>


<pre class="code">
ij> DELETE FROM CARS2 WHERE ID=8;
1 row inserted/updated/deleted
</pre>

<p>
We used the DELETE FROM statement to delete a rown in the table.
</p>

<pre class="code">
ij> DELETE FROM CARS2;
7 rows inserted/updated/deleted
</pre>

<p>
The DELETE FROM statement without the WHERE clause deletes all rows
in the table. 
</p>

<pre class="code">
ij> DROP TABLE CARS2;
0 rows inserted/updated/deleted
</pre>

<p>
The DROP TABLE statement deletes the table completly from the database.
</p>


<h2>RENAME statements</h2>

<p>
The RENAME statement belongs to the data definition language of the SQL. 
</p>

<pre class="code">
ij> RENAME TABLE FRIENDS TO MYFRIENDS;
</pre>

<p>
The RENAME TABLE statement allows us to rename an existing table. We
rename a FRIENDS table to MYFRIENDS. 
</p>

<pre class="code">
ij> RENAME COLUMN MYFRIENDS.ID TO FID;
</pre>

<p>
The RENAME COLUMN statement renames a particular table column
</p>

<p>
In the chapter, we have worked with the basics of the SQL language in Derby.
</p>

<div class="botNav, center">
<span class="botNavItem"><a href="/">Home</a></span> ‡ <span class="botNavItem"><a href="..">Contents</a></span> ‡
<span class="botNavItem"><a href="#">Top of Page</a></span>
</div>


<div class="footer">
<div class="signature">
<a href="/">ZetCode</a> last modified March 5, 2012  <span class="copyright">&copy; 2007 - 2013 Jan Bodnar</span>
</div>
</div>

</div> <!-- content -->

</div> <!-- container -->

</body>
</html>

